package com.osfw.dbadminer.service.jdbc;

import cn.hutool.core.util.NumberUtil;
import com.osfw.dbadminer.enums.SQLStmtType;
import com.osfw.dbadminer.util.SQLParserUtils;
import com.osfw.framework.exception.ServiceException;
import com.osfw.dbadminer.domain.database.*;
import com.osfw.dbadminer.entity.connect.DbaConnect;
import com.osfw.dbadminer.enums.ConnectType;
import com.osfw.dbadminer.service.connect.IDbaConnectService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.util.StopWatch;

import java.sql.*;
import java.util.*;

/**
 * JDBC连接管理 服务层
 */
@Slf4j
@Component
public abstract class JdbcBaseService implements JdbcAsyncCallBack {

    protected String connect_prefix, default_database, connect_params;
    protected Driver driver;
    protected DbaConnect myconnect;

    @Autowired
    protected IDbaConnectService connectService;


    public abstract void init(DbaConnect connect) throws SQLException;

    public abstract List<String> getDatabaseList() throws SQLException;

    public abstract List<TableDef> getTableList(String database) throws SQLException;

    public abstract List<String> getViewList(String database) throws SQLException;

    public abstract List<String> getFunctionList(String database) throws SQLException;

    public abstract List<String> getIndexList(String database) throws SQLException;

    public abstract List<String> getSequenceList(String database) throws SQLException;

    public abstract List<String> getTypeList(String database) throws SQLException;

    public abstract Map<String, Object> getHintTableColumn(HintSqlParam hintSqlParam) throws SQLException;

    public abstract List<String> getAllColumns(String database, String tableName) throws SQLException;

    public abstract List<String> getAllIndex(String database, String tableName) throws SQLException;

    public abstract boolean createTable(TableDef tableDef) throws SQLException;

    public abstract Object getTableContent(SqlExecuteParam sqlExecuteParam) throws SQLException;

    // 构建链接数据库的 url
    public abstract String buildConnectUrl(String database);


    // 执行 sql
    public Object execute(SqlExecuteParam param) throws SQLException {
        Connection connection = getConnection(param.getDatabaseName());
        Statement statement = connection.createStatement();
        if (param.getSqlCode().startsWith("select") || param.getSqlCode().startsWith("SELECT")) {
            return statement.executeQuery(param.getSqlCode());
        } else if (param.getSqlCode().startsWith("update") || param.getSqlCode().startsWith("UPDATE")
                || param.getSqlCode().startsWith("delete") || param.getSqlCode().startsWith("DELETE")) {
            return statement.executeLargeUpdate(param.getSqlCode());
        } else {
            return statement.execute(param.getSqlCode());
        }
    }

    // 执行sql语句
    public SqlExecuteResult executeStmt(String database, String sql, Integer page, Integer size) {
        SqlExecuteResult executeResult;
        SQLStmtType stmtType = SQLParserUtils.getStatementType(sql);
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        StopWatch stopWatch = new StopWatch();
        List<Integer> numberTypes = new ArrayList(Arrays.asList(Types.BIT, Types.TINYINT, Types.SMALLINT, Types.INTEGER, Types.BIGINT, Types.FLOAT, Types.REAL, Types.DOUBLE, Types.NUMERIC, Types.DECIMAL));
        try {
            connection = getConnection(database);
            statement = connection.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
            statement.setQueryTimeout(480); // 8min timeout
            switch (stmtType) {
                case QUERY:
                    stopWatch.start();
                    Long total = 1l;
                    if (!SQLParserUtils.isIndependentCountStatement(sql)) {
                        resultSet = statement.executeQuery(SQLParserUtils.getCountStatements(sql));
                        while (resultSet.next()) {
                            total = resultSet.getLong(1);
                        }
                    }
                    statement.setFetchSize(ConnectType.POSTGRESQL.getType().equals(myconnect.getType()) ? 1000 : Integer.MIN_VALUE);
                    resultSet = statement.executeQuery(sql);
                    stopWatch.stop();

                    List<TableColDef> columns = new ArrayList<>();
                    for (int i = 1; i <= resultSet.getMetaData().getColumnCount(); i++) {
                        TableColDef tableColDef = new TableColDef();
                        tableColDef.setTableName(resultSet.getMetaData().getTableName(i));
                        tableColDef.setColumnName(resultSet.getMetaData().getColumnName(i));
                        tableColDef.setColumnType(resultSet.getMetaData().getColumnType(i));
                        tableColDef.setColumnTypeName(resultSet.getMetaData().getColumnTypeName(i));
                        tableColDef.setColumnLength(resultSet.getMetaData().getColumnDisplaySize(i));
                        tableColDef.setAllowNull(resultSet.getMetaData().isNullable(i) == 1 ? true : false);
                        tableColDef.setIsNumberColumn(numberTypes.contains(resultSet.getMetaData().getColumnType(i)) ? true : false);
                        tableColDef.setMaxCharsCount(tableColDef.getColumnName().chars().count());
                        columns.add(tableColDef);
                    }
                    List<Map<String, Object>> list = new ArrayList<>();
                    int rowNumber = 0, pageCount = 0;
                    while (resultSet.next() && pageCount < size) {
                        rowNumber++;
                        if (rowNumber >= (page - 1) * size) {
                            Map<String, Object> map = new LinkedHashMap<>();
                            for (TableColDef column : columns) {
                                map.put(column.getColumnName(), resultSet.getString(column.getColumnName()));
                                if (resultSet.getString(column.getColumnName()) != null) {
                                    long colCharsCount = column.getColumnName().chars().count();
                                    long valCharsCount = resultSet.getString(column.getColumnName()).chars().count();
                                    if (column.getMaxCharsCount() == null) {
                                        column.setMaxCharsCount(valCharsCount > colCharsCount ? valCharsCount : colCharsCount);
                                    } else {
                                        column.setMaxCharsCount(valCharsCount > column.getMaxCharsCount() ? valCharsCount : column.getMaxCharsCount());
                                    }
                                }
                            }
                            list.add(map);
                            pageCount++;
                        }
                    }

                    executeResult = SqlExecuteResult.success(stmtType, sql, "OK", NumberUtil.round(stopWatch.getTotalTimeSeconds(), 3).doubleValue(), total, page, size, columns, list);
                    break;
                case CALL:
                    stopWatch.start();
                    resultSet = statement.executeQuery(sql);
                    stopWatch.stop();

                    List<TableColDef> columns2 = new ArrayList<>();
                    for (int i = 1; i <= resultSet.getMetaData().getColumnCount(); i++) {
                        TableColDef tableColDef = new TableColDef();
                        tableColDef.setTableName(resultSet.getMetaData().getTableName(i));
                        tableColDef.setColumnName(resultSet.getMetaData().getColumnName(i));
                        tableColDef.setColumnType(resultSet.getMetaData().getColumnType(i));
                        tableColDef.setColumnTypeName(resultSet.getMetaData().getColumnTypeName(i));
                        tableColDef.setColumnLength(resultSet.getMetaData().getColumnDisplaySize(i));
                        tableColDef.setAllowNull(resultSet.getMetaData().isNullable(i) == 1 ? true : false);
                        tableColDef.setIsNumberColumn(numberTypes.contains(resultSet.getMetaData().getColumnType(i)) ? true : false);
                        tableColDef.setMaxCharsCount(tableColDef.getColumnName().chars().count());
                        columns2.add(tableColDef);
                    }
                    List<Map<String, Object>> list2 = new ArrayList<>();
                    while (resultSet.next()) {
                        Map<String, Object> map = new LinkedHashMap<>();
                        for (TableColDef column : columns2) {
                            map.put(column.getColumnName(), resultSet.getString(column.getColumnName()));
                            if (resultSet.getString(column.getColumnName()) != null) {
                                long colCharsCount = column.getColumnName().chars().count();
                                long valCharsCount = resultSet.getString(column.getColumnName()).chars().count();
                                if (column.getMaxCharsCount() == null) {
                                    column.setMaxCharsCount(valCharsCount > colCharsCount ? valCharsCount : colCharsCount);
                                } else {
                                    column.setMaxCharsCount(valCharsCount > column.getMaxCharsCount() ? valCharsCount : column.getMaxCharsCount());
                                }
                            }
                        }
                        list2.add(map);
                    }

                    executeResult = SqlExecuteResult.success(stmtType, sql, "OK", NumberUtil.round(stopWatch.getTotalTimeSeconds(), 3).doubleValue(), Long.valueOf(list2.size()), 1, list2.size(), columns2, list2);
                    break;
                case EXPLAIN:
                    return SqlExecuteResult.warn(stmtType, sql, "暂不支持执行计划语句", 0d);
                case MODIFY:
                    stopWatch.start();
                    Long rows = statement.executeLargeUpdate(sql);
                    stopWatch.stop();
                    executeResult = SqlExecuteResult.success(stmtType, sql, rows.toString(), NumberUtil.round(stopWatch.getTotalTimeSeconds(), 3).doubleValue());
                    break;
                default:
                    stopWatch.start();
                    boolean res = statement.execute(sql);
                    stopWatch.stop();
                    executeResult = SqlExecuteResult.success(stmtType, sql, "OK", NumberUtil.round(stopWatch.getTotalTimeSeconds(), 3).doubleValue());
            }
            return executeResult;

        } catch (SQLException e) {
            return SqlExecuteResult.warn(stmtType, sql, e.getMessage(), 0d);
        } catch (Exception e) {
            return SqlExecuteResult.error(stmtType, sql, e.getMessage(), 0d);
        } finally {
            Connection finalConnect = connection;
            Statement finalStatement = statement;
            ResultSet finalResultSet = resultSet;
            new Thread(() -> {
                new JdbcAsyncFactory().executeAsyncTask(this, "关闭数据库连接资源", finalConnect, finalStatement, finalResultSet);
            }).start();
        }

    }

    // 获取连接
    protected Connection getConnection(String database) {
        String url = buildConnectUrl(database);
        try {
            DriverManager.registerDriver(driver);
            DriverManager.setLoginTimeout(5); // 10min timeout
            return DriverManager.getConnection(url, myconnect.getUsername(), myconnect.getPassword());
        } catch (Exception e) {
            ServiceException.throw500("获取连接异常：" + e.getMessage());
        }
        return null;
    }

    @Override
    public void solve(String result, Connection connect) {
        log.info(result);
        try {
            if (connect != null) {
                connect.close();
            }
        } catch (SQLException e) {
            log.error("Connect关闭异常", e);
        }
    }

}
